package main

import (
	"fmt"
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
	"time"
)


func main(){
	fmt.Println("hello sqlite3")
	
	db,err:=sql.Open("sqlite3","d:\\foo.db")
	checkErr(err)
	
	//insert
	stmt,err:=db.Prepare("insert into userinfo(username,departname,created) values(?,?,?)")
	checkErr(err)
	
	res,err:=stmt.Exec("lucy","RD","2015-06-23")
	checkErr(err)
	
	id,err:=res.LastInsertId()
	checkErr(err)
	fmt.Println("id = ",id)
	
	//update
	stmt,err=db.Prepare("update userinfo set username=? where uid=?")
	checkErr(err)
	
	res,err=stmt.Exec("Linda",id)
	checkErr(err)
	
	affect,err:=res.RowsAffected()
	checkErr(err)
	
	fmt.Println("affected of update:",affect)
	
	//query
	rows,err:=db.Query("select * from userinfo")
	checkErr(err)
	
	for rows.Next() {
		var uid int
		var username string
		var department string
		var created time.Time
		err=rows.Scan(&uid,&username,&department,&created)
		checkErr(err)
		fmt.Println("id=",uid)
		fmt.Println("username=",username)
		fmt.Println("department=",department)
		fmt.Println("created=",created)
	}
	
	//delete
	stmt,err=db.Prepare("delete from userinfo where uid=?")
	checkErr(err)
	
	res,err=stmt.Exec(id)
	
	affect,err=res.RowsAffected()
	checkErr(err)
	
	fmt.Println("affected of delete:",affect)
	
	db.Close()	
}

func checkErr(err error){
	if err!=nil {
		panic(err)
	}
}
/*
CREATE TABLE `userinfo` (
    `uid` INTEGER PRIMARY KEY AUTOINCREMENT,
    `username` VARCHAR(64) NULL,
    `departname` VARCHAR(64) NULL,
    `created` DATE NULL
);

CREATE TABLE `userdeatail` (
    `uid` INT(10) NULL,
    `intro` TEXT NULL,
    `profile` TEXT NULL,
    PRIMARY KEY (`uid`)
);
*/
